In this article, we’ll show how to create an XML data type document from a relational table utilizing different T-SQL ways. For example, for data migration, information from the SQL Server database can be exported and imported using XML in another framework. XML is a standard way of extracting, storing and manipulating data. One aspect of working with the XML data type is creating XML from relational information, which is done using the FOR XML provision in SQL Server:
An XML data type can be utilized in an alternate manner in SQL Server. This article will explain some fundamental uses of XML. FOR XML keyword is a provision that can be added with the SELECT query statement to prepare the XML document in the required forms in a query result. The outcome is a Unicode string containing components and qualities controlled by the different modes determined in the provision with FOR XML.
The four modes are:
- RAW
- AUTO
- EXPLICIT
- PATH
The table below shows sample data for further clarification of the various methodologies:
AUTO mode
AUTO mode is one of the approaches to converts over to the XML elements from the rows of the table. Element names of the XML document corresponding to the column alias or column name of the SELECT query.
For example, the query result has been produced with a single XML document for the above sample data. The query result had 5 rows in a result set, which is changed over to a solitary cell result-set. This methodology is utilized in a single-valued function to return multiple rows in a single return variable.
|
1 2 3 |
SELECT user_id, fname, lname, email_ FROM user_details FOR XML AUTO; |
Here, AUTO produces header names utilizing table names.
EXPLICIT mode
For more control over the styling of the subsequent XML, SQL developers can use EXPLICIT mode. With EXPLICIT mode, a query result will be changed over to a single XML data type document with customized XML labels and values. The EXPLICIT mode will make a different label set for each row in a record. Even, the EXPLICIT mode allows designing the XML format as the user wants to set the position of the elements in the XML data type format in SQL Query.
Below is an example of using EXPLICIT mode:
SQL query:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SELECT 1 as tag, NULL as parent, user_id as [user!1!ID], NULL AS [fname!2!fname], NULL AS [lname!3!lname], NULL AS [email!4!email] FROM user_details UNION ALL SELECT 3 as tag, 1 as parent, user_id as [user!1!ID], fname AS [fname!2!fname], lname AS [lname!3!lname], email_ AS [email!4!email] FROM user_details UNION ALL SELECT 2 as tag, 1 as parent, user_id as [user!1!ID], fname AS [fname!2!fname], lname AS [lname!3!lname], email_ AS [email!4!email] FROM user_details ORDER BY 3, 1 FOR XML EXPLICIT; |
EXPLICIT mode result:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
<user ID="1"> <fname fname="Jo" /> <lname lname="Brown" /> </user> <user ID="2"> <fname fname="Katie" /> <lname lname="McAskill-White" /> </user> <user ID="3"> <fname fname="Mihail" /> <lname lname="Frintu" /> </user> <user ID="4"> <fname fname="Jimmy" /> <lname lname="Bischoff" /> </user> <user ID="5"> <fname fname="Alan" /> <lname lname="Brewer" /> </user> |
RAW mode
The most common and often-used mode by developers for making XML within FOR XML is the RAW mode. Basically, RAW mode outputs an element named “row” from the result set for each row in the SELECT articulation, and the cell value of the row will be bounded as an attribute of the element. In a RAW mode, every column will be treated as one element in the XML, and columns of those lines will be attributed to the same element. RAW mode is used to change each row in the result set into an XML component.
For ROW mode, various querying models with different outputs explained in the examples below:
Example 1
|
1 2 3 4 5 6 7 8 9 |
SELECT user_id, fname, lname, email_ FROM user_details FOR XML RAW <row user_id="1" fname="Jo" lname="Brown" email_="jo0@adventure-works.com" /> <row user_id="2" fname="Katie" lname="McAskill-White" email_="katie0@adventure-works.com" /> <row user_id="3" fname="Mihail" lname="Frintu" email_="mihail0@adventure-works.com" /> <row user_id="4" fname="Jimmy" lname="Bischoff" email_="jimmy0@adventure-works.com" /> <row user_id="5" fname="Alan" lname="Brewer" email_="alan0@adventure-works.com" /> |
Using RAW mode, the header of each element is defined as a row element in the XML result and the columns of each row are being attributed inside the row element in the XML data type result.
Example 2
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT user_id, fname, lname, email_ FROM user_details FOR XML RAW('user'), ROOT <root> <user user_id="1" fname="Jo" lname="Brown" email_="jo0@adventure-works.com" /> <user user_id="2" fname="Katie" lname="McAskill-White" email_="katie0@adventure-works.com" /> <user user_id="3" fname="Mihail" lname="Frintu" email_="mihail0@adventure-works.com" /> <user user_id="4" fname="Jimmy" lname="Bischoff" email_="jimmy0@adventure-works.com" /> <user user_id="5" fname="Alan" lname="Brewer" email_="alan0@adventure-works.com" /> </root> |
If the user wants to include all elements in a single tag, then the ROOT option with the RAW mode can be used and each element’s header name can be defined by the user by adding a name with the RAW() in the XML data type result.
Example 3
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT user_id, fname, lname, email_ FROM user_details FOR XML RAW('user'), ROOT('UserDetails') <UserDetails> <user user_id="1" fname="Jo" lname="Brown" email_="jo0@adventure-works.com" /> <user user_id="2" fname="Katie" lname="McAskill-White" email_="katie0@adventure-works.com" /> <user user_id="3" fname="Mihail" lname="Frintu" email_="mihail0@adventure-works.com" /> <user user_id="4" fname="Jimmy" lname="Bischoff" email_="jimmy0@adventure-works.com" /> <user user_id="5" fname="Alan" lname="Brewer" email_="alan0@adventure-works.com" /> </UserDetails> |
In the XML RAW ROOT, the header can be designed with a user-defined name with the ROOT(). As it can be seen in the example, the <root> element is replaced by the <UserDetails>
Example 4
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT user_id, fname, lname, email_ FROM user_details FOR XML RAW, ELEMENTS <row> <user_id>1</user_id> <fname>Jo</fname> <lname>Brown</lname> <email_>jo0@adventure-works.com</email_> </row> <row> <user_id>2</user_id> <fname>Katie</fname> <lname>McAskill-White</lname> <email_>katie0@adventure-works.com</email_> </row> |
Using the ELEMENT directive with the RAW mode, the user can make row-element structure-based XML. Each cell of the row is being extracted with XML elements inside the parent element of the row as <row>. Basically, we used the ELEMENTS keyword in the above query to display the columns as elements in the output XML.
PATH mode
The best mode for creating XML with more control over the format is the PATH mode. Using the PATH, the user can compose more straightforward SELECT and supply XPATH articulations for the column to assign a chain of command. Use FOR XML PATH with subqueries to get the proper XML data type document with the element-attribute chain:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT user_id, fname, lname, email_ FROM user_details WHERE email_ = 'mihail0@adventure-works.com' FOR XML PATH; <row> <user_id>3</user_id> <fname>Mihail</fname> <lname>Frintu</lname> <email_>mihail0@adventure-works.com</email_> </row> |
Conclusion
Using a server query result to achieve a different XML format is straightforward. The query result will be extracted in XML by including a FOR XML catchphrase at the end of the SELECT statement. As discussed, we have a number of alternatives to structure or arrange the outcome with columns to attributes or XML nodes with the XML data type.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020